#!/bin/bash

# 定义变量方便修改
DATABASE=gmall

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
   do_date=$2
else
   do_date=`date -d "-1 day" +%F`
fi


# 用户维度表（拉链表）-- 每日 -- 拉链表
dim_user_info="
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
WITH new_user AS (
    -- 1. 每日新数据--2023-09-02
    SELECT
        id,
        login_name,
        nick_name,
        md5(name) AS name,
        md5(phone_num) AS phone_num,
        md5(email) AS email,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        '${do_date}' start_date,
        '9999-12-31' end_date
    from ${DATABASE}.ods_user_info
    where dt = '${do_date}'
), old_user AS (
    -- 2. 昨日所有数据--9999-12-31
    select
        id,
        login_name,
        nick_name,
        name,
        phone_num,
        email,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        start_date,
        end_date
    from ${DATABASE}.dim_user_info
    where dt = '9999-12-31'
)
INSERT OVERWRITE TABLE ${DATABASE}.dim_user_info PARTITION(dt)
-- 1. 新数据和更新数据
SELECT
    id,
    login_name,
    nick_name,
    name,
    phone_num,
    email,
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    start_date,
    end_date,
    end_date AS dt
FROM new_user
UNION ALL
-- 2. 老数据，如果是更新的数据，需要修改老数据结束时间
SELECT
    old_user.id,
    old_user.login_name,
    old_user.nick_name,
    old_user.name,
    old_user.phone_num,
    old_user.email,
    old_user.user_level,
    old_user.birthday,
    old_user.gender,
    old_user.create_time,
    old_user.operate_time,
    old_user.start_date,
    if(new_user.id IS NULL, old_user.end_date, date_sub(new_user.start_date, 1)) AS end_date,
    if(new_user.id IS NULL, old_user.end_date, date_sub(new_user.start_date, 1)) AS dt
FROM old_user
LEFT JOIN new_user ON old_user.id = new_user.id;
"


# 商品维度表（全量）-- 每日
dim_sku_info="
SET hive.stats.column.autogather = false ;
WITH sku AS (
    -- 1. 商品sku信息表
    SELECT
        id,
        price,
        sku_name,
        sku_desc,
        weight,
        is_sale,
        spu_id,
        category3_id,
        tm_id,
        create_time
    FROM ${DATABASE}.ods_sku_info
    WHERE dt = '${do_date}'
), spu AS (
    -- 2. 商品spu信息表
    SELECT
        id,
        spu_name
    FROM ${DATABASE}.ods_spu_info
    WHERE dt = '${do_date}'
), c3 AS (
    -- 3. 三级品类表
    SELECT
        id,
        name,
        category2_id
    FROM ${DATABASE}.ods_base_category3
    WHERE dt = '${do_date}'
), c2 AS (
    -- 4. 二级品类表
    SELECT
        id,
        name,
        category1_id
    FROM ${DATABASE}.ods_base_category2
    WHERE dt = '${do_date}'
), c1 AS (
    -- 5. 一级品类表
    SELECT
        id,
        name
    FROM ${DATABASE}.ods_base_category1
    WHERE dt = '${do_date}'
), tm AS (
    -- 6. 品牌表
    SELECT
        id,
        tm_name
    FROM ${DATABASE}.ods_base_trademark
    WHERE dt = '${do_date}'
), attr AS (
    -- 7. sku平台属性值
    SELECT
        sku_id,
        collect_set(
            named_struct(
                'attr_id', attr_id, 'value_id', value_id, 'attr_name', attr_name, 'value_name', value_name
            )
        ) attrs
    FROM ${DATABASE}.ods_sku_attr_value
    WHERE dt = '${do_date}'
    GROUP BY sku_id
), sale_attr AS (
    -- 8. sku销售属性值
    SELECT
        sku_id,
        collect_set(
            named_struct(
                'sale_attr_id', sale_attr_id, 'sale_attr_value_id', sale_attr_value_id,
                'sale_attr_name', sale_attr_name, 'sale_attr_value_name', sale_attr_value_name
            )
        ) sale_attrs
    FROM ${DATABASE} .ods_sku_sale_attr_value
    WHERE dt = '${do_date}'
    GROUP BY sku_id
)
INSERT OVERWRITE TABLE ${DATABASE}.dim_sku_info PARTITION(dt = '${do_date}')
SELECT
    sku.id,
    sku.price,
    sku.sku_name,
    sku.sku_desc,
    sku.weight,
    sku.is_sale,
    sku.spu_id,
    spu.spu_name,
    sku.category3_id,
    c3.name,
    c3.category2_id,
    c2.name,
    c2.category1_id,
    c1.name,
    sku.tm_id,
    tm.tm_name,
    attr.attrs,
    sale_attr.sale_attrs,
    sku.create_time
FROM sku
LEFT JOIN spu ON sku.spu_id = spu.id
LEFT JOIN c3 ON sku.category3_id = c3.id
LEFT JOIN c2 ON c3.category2_id = c2.id
LEFT JOIN c1 ON c2.category1_id = c1.id
LEFT JOIN tm ON sku.tm_id = tm.id
LEFT JOIN attr ON sku.id = attr.sku_id
LEFT JOIN sale_attr ON sku.id = sale_attr.sku_id;
"

# 优惠券维度表（全量）-- 每日
dim_coupon_info="
SET hive.stats.column.autogather = false ;
INSERT OVERWRITE TABLE ${DATABASE}.dim_coupon_info PARTITION(dt = '${do_date}')
SELECT
    id,
    coupon_name,
    coupon_type,
    condition_amount,
    condition_num,
    activity_id,
    benefit_amount,
    benefit_discount,
    create_time,
    range_type,
    limit_num,
    taken_count,
    start_time,
    end_time,
    operate_time,
    expire_time
FROM ${DATABASE}.ods_coupon_info
WHERE dt = '${do_date}';
"

# 活动维度表（全量）-- 每日
dim_activity_rule_info="
SET hive.stats.column.autogather = false ;
WITH ar AS (
    -- 1. 活动规则表
    SELECT
        id,
        activity_id,
        activity_type,
        condition_amount,
        condition_num,
        benefit_amount,
        benefit_discount,
        benefit_level
    FROM ${DATABASE}.ods_activity_rule
    WHERE dt = '${do_date}'
), ai AS (
    -- 2. 活动信息表
    SELECT
        id,
        activity_name,
        start_time,
        end_time,
        create_time
    FROM ${DATABASE}.ods_activity_info
    WHERE dt = '${do_date}'
)
INSERT OVERWRITE TABLE ${DATABASE}.dim_activity_rule_info PARTITION(dt = '${do_date}')
SELECT
    ar.id,
    ar.activity_id,
    ai.activity_name,
    ar.activity_type,
    ai.start_time,
    ai.end_time,
    ai.create_time,
    ar.condition_amount,
    ar.condition_num,
    ar.benefit_amount,
    ar.benefit_discount,
    ar.benefit_level
FROM ar
LEFT JOIN ai ON ar.activity_id = ai.id;
"


case $1 in
    "dim_user_info"){
        hive -e "$dim_user_info"
    };;
    "dim_sku_info"){
        hive -e "$dim_sku_info"
    };;
    "dim_coupon_info"){
        hive -e "$dim_coupon_info"
    };;
    "dim_activity_rule_info"){
        hive -e "$dim_activity_rule_info"
    };;
    "all"){
        hive -e "$dim_user_info$dim_sku_info$dim_coupon_info$dim_activity_rule_info"
    };;
esac


#
# 执行权限
# chmod u+x ods_to_dim_db.sh
# 执行脚本，首日数据导入
# sh ods_to_dim_db.sh dim_user_info 2023-09-01
# sh ods_to_dim_db.sh dim_sku_info 2023-09-01
# sh ods_to_dim_db.sh dim_coupon_info 2023-09-01
# sh ods_to_dim_db.sh dim_activity_rule_info 2023-09-01
# sh ods_to_dim_db.sh all 2023-09-01
#

